package com.sztx.pay.center.tool;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class AccountCheckingExcel {

	@SuppressWarnings("resource")
	public static void main(String[] args) throws IOException {

		InputStream sztxInputStream = new FileInputStream("C:\\Users\\adminstrator\\Desktop\\duizhang\\sztx.xlsx");
		InputStream thirdInputStream = new FileInputStream("C:\\Users\\adminstrator\\Desktop\\duizhang\\other.xlsx");
		int sztxColumn = 4;
		int thirdColumn = 9;

		Workbook sztxWorkbook = new XSSFWorkbook(sztxInputStream);
		Workbook thirdPartyWorkbook = new XSSFWorkbook(thirdInputStream);

		Sheet sztxSheet = sztxWorkbook.getSheetAt(0);
		List<String> sztxOrderNos = new ArrayList<String>();
		for (int rowNum = 1; rowNum <= sztxSheet.getLastRowNum(); rowNum++) {
			Row row = sztxSheet.getRow(rowNum);
			row.getCell(sztxColumn - 1).setCellType(Cell.CELL_TYPE_STRING);
			Cell cell = row.getCell(sztxColumn - 1);
			String sztxOrderNo = cell.getStringCellValue();
			if (!isEmpty(sztxOrderNo)) {
				sztxOrderNos.add(sztxOrderNo.trim());
			}
		}

		Sheet thirdPartySheet = thirdPartyWorkbook.getSheetAt(0);
		List<String> thirdPartyOrderNos = new ArrayList<String>();
		for (int rowNum = 1; rowNum <= thirdPartySheet.getLastRowNum(); rowNum++) {
			Row thirdPartyRow = thirdPartySheet.getRow(rowNum);
			thirdPartyRow.getCell(sztxColumn - 1).setCellType(Cell.CELL_TYPE_STRING);
			Cell cell = thirdPartyRow.getCell(thirdColumn - 1);
			String thirdPartyOrderNo = cell.getStringCellValue();

			if (!isEmpty(thirdPartyOrderNo)) {
				if (thirdPartyOrderNo.startsWith("`")) {
					thirdPartyOrderNo = thirdPartyOrderNo.substring(1, thirdPartyOrderNo.length());
				}
				thirdPartyOrderNos.add(thirdPartyOrderNo.trim());
			}
		}

		List<String> sztxConstansList = new ArrayList<String>();
		sztxConstansList.addAll(sztxOrderNos);
		sztxConstansList.removeAll(thirdPartyOrderNos);
		System.out.println("sztx有第三方没有:" + sztxConstansList);
		System.out.println("共:" + sztxConstansList.size() + "条");

		List<String> thirdConstansList = new ArrayList<String>();
		thirdConstansList.addAll(thirdPartyOrderNos);
		thirdConstansList.removeAll(sztxOrderNos);
		System.out.println("第三方有sztx没有:" + thirdConstansList);
		System.out.println("共:" + thirdConstansList.size() + "条");

		List<String> intersection = getIntersection(sztxOrderNos, thirdPartyOrderNos);
		System.out.println("共同拥有:" + intersection);
		System.out.println("共:" + intersection.size() + "条");

	}

	public static List<String> getIntersection(List<String> list1, List<String> list2) {
		List<String> result = new ArrayList<String>();
		for (String str : list2) {// 遍历list1
			if (list1.contains(str)) {// 如果存在这个数
				result.add(str);// 放进一个list里面，这个list就是交集
			}
		}
		return result;
	}

	public static boolean isEmpty(String str) {
		if (str == null || str.length() == 0)
			return true;
		return false;
	}

	public static boolean isNumeric(String str) {
		for (int i = str.length(); --i >= 0;) {
			if (!Character.isDigit(str.charAt(i))) {
				return false;
			}
		}
		return true;
	}

}
